﻿CREATE PROCEDURE [acms].[Post_Put]
	@Id int, 
	@SiteId int, 
	@PostGuid uniqueidentifier, 
	@ParentGuid uniqueidentifier, 
	@Name varchar(100), 
	@DisplayName varchar(100), 
	@TemplateId int, 
	@ThumbnailId int,
	@Summary varchar(2000), 
	@Sequence int, 
	@PublishFrom DateTime, 
	@PublishTo DateTime,
	@Notes nvarchar(2000),
	@RequiresPublishing bit,
	@ReadyToPublish bit,
	@CreatedDateTime datetime,
	@DeveloperLocked bit,
	@ConcurencyId timestamp,
	@IdOut int output, 
	@SiteNameOut varchar(50) output, 
	@ParentPathOut varchar(255) output, 
	@TemplateNameOut varchar(255) output, 
	@NodeTypeOut int output,
	@SequenceOut int output, 
	@ConcurencyIdOut timestamp output
AS

	declare @ParentPath varchar(255)
	declare @ParentSequence varchar(20)
	
	if exists (select null from Post_View with (nolock) where Id = @Id) begin
		
		
		update [Posts]
		set
			SiteId = @SiteId,
			ParentGuid = @ParentGuid,
			Name = @Name,
			DisplayName = @DisplayName,
			TemplateId = @TemplateId,
			ThumbnailId = @ThumbnailId,
			Summary = @Summary,
			Sequence = @Sequence,
			PublishFrom = @PublishFrom,
			PublishTo = @PublishTo,
			RequiresPublishing = @RequiresPublishing,
			ReadyToPublish = @ReadyToPublish,
			Notes = @Notes,
			DeveloperLocked = @DeveloperLocked
		where
			Id = @Id
			and VersionId = @ConcurencyId
		if @@RowCount <> 1 
			return -100
		else
			set @IdOut = @Id
		
	end
	else if @Id is null or @Id = 0 begin
		if(@PublishFrom is null) begin
			select @Sequence = isNull(Max(a.Sequence) + 1, 0) 
			from Post_View a with (nolock) 
				inner join PostPositions b with(nolock) on a.Id = b.PostId and b.PublishDate is null
			where ParentGuid = @ParentGuid	
		end
		else begin
			select @Sequence = isNull(Max(a.Sequence) + 1, 0) 
			from Post_View a with (nolock) 
				inner join PostPositions b with(nolock) on a.Id = b.PostId and b.PublishDate >= @PublishFrom and b.PublishDate < isNull(@PublishTo, '9999/12/31')
			where ParentGuid = @ParentGuid	
		end

		select @Sequence = isNull(Max(Post_View.Sequence) + 1, 0) 
		from Post_View with (nolock) 
			inner join PostPositions with(nolock) on Post_View.Id = PostPositions.PostId and PostPositions.PublishDate is null
		where ParentGuid = @ParentGuid	
		
		insert [Posts]
			(SiteId, PostGuid, ParentGuid, Name, DisplayName, Sequence, TemplateId, ThumbnailId, Summary, PublishFrom, PublishTo, RequiresPublishing, ReadyToPublish, Notes, CreatedDateTime, DeveloperLocked)
		values
			(@SiteId, @PostGuid, @ParentGuid, @Name, @DisplayName, @Sequence, @TemplateId, @ThumbnailId, @Summary, @PublishFrom, @PublishTo, @RequiresPublishing, @ReadyToPublish, @Notes, @CreatedDateTime, @DeveloperLocked)
		
		set @IdOut = @@Identity
		
		select @ParentPath = IsNull(b.ParentPath, '') + a.Name + '/', @ParentSequence = IsNull(b.ParentSequence, '') + a.Sequence from Posts a with (nolock) inner join PostPositions b with (nolock) on a.Id = b.PostId and a.PostGuid = @ParentGuid and isNull(a.PublishFrom, '9999/12/31') = isNull(b.PublishDate, '9999/12/31')
		
		insert into PostPositions
			(PostId, PublishDate, ParentPath, ParentSequence, Sequence)
		values
			(@IdOut, @PublishFrom, @ParentPath, @ParentSequence, @Sequence)
	end
	else begin
		return -200
	end
	
	--sort out post postions
	--step 1: delete any post positions for theis post that are outside the date range
	delete acms.PostPositions
	from acms.PostPositions a
		inner join acms.Posts b on a.PostId = b.Id
	where b.Id = @IdOut
		and 
			(IsNull(a.PublishDate, '9999/12/31') < IsNull(@PublishFrom, '9999/12/31')
			or
			IsNull(a.PublishDate, '9999/12/31') >= IsNull(@PublishTo, '9999/12/31'))

	--step 2: Delete out any other posts that overlap
	if @PublishFrom is not null and Exists(select null from Post_View with(nolock) where PostGuid = @PostGuid and PublishFrom is not null and IsNull(PublishTo, '9999/12/31') > @PublishFrom) begin
		update Post_View Set PublishTo = @PublishFrom
		Where PostGuid = @PostGuid
			and PublishFrom is not null 
			and IsNull(PublishTo, '9999/12/31') > @PublishFrom
			and Id <> @IdOut
		
		--delete out any unnessary post positions
		delete acms.PostPositions
		from acms.PostPositions a
			inner join acms.Posts b on a.PostId = b.Id
		where b.Id <> @Id and b.PostGuid = @PostGuid and a.PublishDate >= b.PublishTo
	end

	--step 3: build tree for each postposition that is with in publish dates
	if @PublishFrom is null begin
		if exists (select null from acms.PostPositions where PublishDate is null)
			exec acms.PostPosition_BuildTree @SiteId, @ParentGuid, null
		else
			exec acms.PostPosition_BuildTree @SiteId, null, null
	end
	else begin
		declare @tmpDates table(PublishDate datetime not null)
		declare @tmpDate datetime
		insert into @tmpDates (PublishDate) values (@PublishFrom)

		insert into @tmpDates (PublishDate)
		select distinct PublishDate
		from acms.PostPositions 
		where PublishDate > @PublishFrom and PublishDate < isNull(@PublishTo, '9999/12/31')
		order by PublishDate
		
		if @PublishTo is not null
			insert into @tmpDates (PublishDate) values (@PublishTo)
		
		declare cur cursor local for select distinct PublishDate from @tmpDates
		open cur
		fetch next from cur into @tmpDate
		while @@fetch_status = 0 begin
			if exists (select null from acms.PostPositions where PublishDate = @tmpDate)
				exec acms.PostPosition_BuildTree @SiteId, @ParentGuid, @tmpDate
			else
				exec acms.PostPosition_BuildTree @SiteId, null, @tmpDate
			fetch next from cur into @tmpDate
		end
		close cur
		deallocate cur
		
	end

	--check there is a pending post if not create it
	if @PublishFrom is not null and not exists (select null from Post_View with (nolock) where PostGuid = @PostGuid and PublishFrom is null) begin
		declare @newPendingId int
		declare @PendingParentPath varchar(255)
		declare @PendingParentSequence varchar(20)

		select @PendingParentPath = IsNull(b.ParentPath, '') + a.Name + '/', @PendingParentSequence = IsNull(b.ParentSequence, '') + a.Sequence from Posts a with (nolock) inner join PostPositions b with (nolock) on a.Id = b.PostId and a.PostGuid = @ParentGuid and a.PublishFrom is null and b.PublishDate is null
		
		insert into Posts
			(SiteId, PostGuid, ParentGuid, Name, DisplayName, ThumbnailId, TemplateId, Summary, Sequence, RequiresPublishing, ReadyToPublish, Notes, CreatedDateTime, DeveloperLocked)
		values
			(@SiteId, @PostGuid, @ParentGuid, @Name, @DisplayName, @ThumbnailId, @TemplateId, @Summary, @Sequence, 0, 0, @Notes, getutcdate(), @DeveloperLocked)
		set @newPendingId = @@identity
		
		insert into PostPositions
			(PostId, PublishDate, ParentPath, ParentSequence, Sequence)
		values
			(@newPendingId, null, @PendingParentPath, @PendingParentSequence, @Sequence)
		
		insert into PostMenu
			(MenuId, PostId, Included)
		select MenuId, @newPendingId, Included
		from PostMenu with(nolock)
		where PostId = @IdOut and deleted = 0

		insert into PostControls
			(PostId, TemplateControlId, Value)
		select @newPendingId, TemplateControlId, Value
		from PostControls with(nolock)
		where PostId = @IdOut and deleted = 0

		insert into PostTopics
			(PostId, TopicId)
		select @newPendingId, TopicId
		from PostTopics with(nolock)
		where PostId = @IdOut and deleted = 0
	end

--return the output parmeters	
	select 
		@SiteNameOut = a.SiteName,
		@NodeTypeOut = a.NodeType,
		@TemplateNameOut = a.TemplateName,
		@SequenceOut = a.Sequence,
		@ConcurencyIdOut = a.VersionId,
		@ParentPathOut = b.ParentPath
	from Post_View a with (nolock)
		inner join acms.PostPositions b with (nolock) on a.Id = b.PostId
	where a.Id = @IdOut
		and IsNull(b.PublishDate, '9999/12/31') = IsNull(@PublishFrom, '9999/12/31')
RETURN 0;

